package dao;
import java.io.FileOutputStream;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class outgrade {

	public static void main(String[] args) {
		ArrayList<String>list=new ArrayList<String>();
		Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
		String sql ="select * from students ";
        conn = JDBCUtil.getConnection();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while(rs.next()){
        	list.add(0,rs.getString("stunum"));
           	list.add(1,rs.getString("name"));
           	list.add(2,rs.getString("classes"));
           	list.add(3,rs.getString("gender"));
           	list.add(4,rs.getString("javaScore"));
           	list.add(5,rs.getString("mathScore"));
           	list.add(6,rs.getString("englishScore"));
           	System.out.println();
        }
		HSSFWorkbook wb=new HSSFWorkbook();//创建Workbook对象（excel的文档对象）
		HSSFSheet sheet=wb.createSheet("表");
		HSSFRow row1=sheet.createRow(0);
		HSSFCell cell=row1.createCell(0);
		cell.setCellValue("学生学习情况报表");
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,9));
        HSSFRow row2=sheet.createRow(1);
        row2.createCell(0).setCellValue("学号");
        row2.createCell(1).setCellValue("名字");
        row2.createCell(2).setCellValue("班级");
        row2.createCell(3).setCellValue("性别");
        row2.createCell(4).setCellValue("JAVA成绩");
        row2.createCell(5).setCellValue("数学成绩");
        row2.createCell(6).setCellValue("英语成绩");
        row2.createCell(7).setCellValue("平均分");
        row2.createCell(8).setCellValue("最高分");
        row2.createCell(9).setCellValue("最低分");
        for(int i=0;i<list.size();i++) {
        	HSSFRow row=sheet.createRow(i+2);
        	 row.createCell(0).setCellValue(list.get(i++));
             row.createCell(1).setCellValue(list.get(i++));
             row.createCell(2).setCellValue(list.get(i++));
             row.createCell(3).setCellValue(list.get(i++));
             row.createCell(4).setCellValue(list.get(i++));
             row.createCell(5).setCellValue(list.get(i++));
             row.createCell(6).setCellValue(list.get(i++));
             row.createCell(7).setCellValue(list.get(i++));
             row.createCell(8).setCellValue(list.get(i++));
             row.createCell(9).setCellValue(list.get(i++));
        }
        try {
        	FileOutputStream fout=new FileOutputStream("G:/students.xls");
        	wb.write(fout);
        	fout.close();
        }catch(Exception e){
        	e.printStackTrace();
        }
	}

}
